package com.typhoon.spring_shiro.utils;

import java.util.LinkedList;

import org.apache.commons.lang3.StringUtils;

public class SqlCommonUtils {
	
	private final static String  SELECT = "select";
	private final static String  FROM = " from ";
	private final static int MAXNUM = 100000;

	public static String getCountSql(String sql){
		return formateSqlForCount(sql);
	}
	
	

	public static String formateSqlForCount(String sql){
		return formateSqlForCount(sql, "1");
	}
	
	public static String formateSqlForCount(String sql, String count){
		sql = sql.replaceAll("(^\\s*\\()|(\\)\\s*$)", "").replaceAll("(?i)select\\s+", "select ").replaceAll("(?i)from\\s+", "from ").replaceAll("(?i)order\\s+by", "order by").replaceAll("(?i)limit", "limit");
		int pointer = sql.indexOf(SELECT, 0);
		//���﷨����ջ
		LinkedList<String> stack  =  new LinkedList<String>();
		stack.push(SELECT);
		while(!stack.isEmpty()){
			String first = stack.getFirst();
			int p1 = sql.indexOf(SELECT, pointer + 4);
			int p2 = sql.indexOf(FROM, pointer + 4);
			p1 = (p1 == -1)? MAXNUM: p1;
			p2 = (p2 == -1)? MAXNUM: p2;
			if(p1 == MAXNUM && p2 == MAXNUM) throw new RuntimeException("SQL���Ƿ�...");
			if(p1 <= p2){
				pointer = p1;
				if(first.equals(SELECT))stack.push(SELECT);
				else stack.pop();
			}else{
				pointer = p2;
				if(first.equals(FROM))stack.push(FROM);
				else stack.pop();
			}
		}		
		int fromIndex = pointer;
		sql = sql.substring(fromIndex);
		int orderIndex = sql.lastIndexOf("order by");
		if(orderIndex != -1 && sql.indexOf(")", orderIndex) < 0){
			sql = sql.substring(0, orderIndex);
		}
		int limitIndex = sql.lastIndexOf("limit");
		if(limitIndex != -1 && sql.indexOf(")", limitIndex) < 0){
			sql = sql.substring(0, limitIndex);
		}
		return "select count("+ count +") " + sql;
	}


	public static String replaceSpecialWord(String querySql){
		querySql = querySql.replaceAll(";", "\\\\;");
		//����һ���͹�����������ٰ�ȫ���...		
		String a2 = "[^\\.~$',_a-zA-Z_0-9]";
		String a1 = "(?i)" + a2;
		querySql = querySql.replaceAll(a1 + "delete" + a2, "--");
		querySql = querySql.replaceAll(a1 + "update" + a2, "--");
		querySql = querySql.replaceAll(a1 + "insert" + a2, "--");
		querySql = querySql.replaceAll(a1 + "replace" + a2,"--");
		return querySql;
	}

	public static String getSubTableName(String tableName, long memberId) {
		long endNum = memberId % 100;

		if (endNum > 9) {
			return new StringBuilder(tableName).append("_").append(endNum)
					.toString();
		} else if (endNum < 0) {
			return new StringBuilder(tableName).toString();
		} else {
			return new StringBuilder(tableName).append("_0").append(endNum)
					.toString();
		}
	}

    public static String getSubTableName(String tableName, String suffix) {
        return new StringBuffer(tableName).append("_").append(suffix).toString();
    }
	

	public static String getMod1000SubTableName(String tableName, Long memberId){
		String mod = memberId % 1000 + "";
		return tableName + "_" + StringUtils.leftPad(mod, 3, "0");
	}
	public static String getMod1000SubTableName(String tableName, Integer memberId){
		return getMod1000SubTableName(tableName,(long)memberId);
	}
	public static void main(String[] args) {
		String abc = SqlCommonUtils.replaceSpecialWord("select a.delete, deletetime from table where a= 1; delete from table; update' set abc = 456;");
		System.out.println(abc);
		String str = getMod1000SubTableName("userAskQuestionIds",1337518117);
		System.out.println(StringUtils.substring(str,str.length()-3));
		for (int i=0; i<1000;++i){
			System.out.println("crmsync "+"SaleCaseProc_"+StringUtils.leftPad(Integer.toString(i), 3, "0") + " 48");

		}
	}
	
}
